package com.thermoberg.passport.dao.impl;


import com.thermoberg.passport.dao.SbyPassportDao;
import com.thermoberg.passport.entity.SbyPassport;
import com.thermoberg.passport.entity.SmsManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by wanghe on 2016/11/2.
 */
@Repository
public class SbyPassportDaoImpl implements SbyPassportDao {
    private JdbcTemplate template;

    SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Timestamp createAt = Timestamp.valueOf(dft.format(new Date()));

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource);
    }

    @Override
    public SbyPassport loginuser(String username, String password) {
        String sql="select id,username,password,userrole from sby_passport where username = ? and password = ?";
        return template.query(sql,new Object[]{username,password},new ResultSetExtractor<SbyPassport>() {
            @Override
            public SbyPassport extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    SbyPassport sbyPassport = new SbyPassport();
                    sbyPassport.setId(rs.getInt("id"));
                    sbyPassport.setUsername(rs.getString("username"));
                    sbyPassport.setPassword(rs.getString("password"));
                    sbyPassport.setUserrole(rs.getInt("userrole"));
                    return sbyPassport;
                } else {
                    return null;
                }
            }
        });
    }

    @Override
    public SbyPassport sbyPassportList(Integer id) {
        String sql="select * from sby_passport where id = ?";
        return template.query(sql,new Object[]{id},new ResultSetExtractor<SbyPassport>() {
            @Override
            public SbyPassport extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    SbyPassport sbyPassport = new SbyPassport();
                    sbyPassport.setId(rs.getInt("id"));
                    sbyPassport.setUsername(rs.getString("username"));
                    sbyPassport.setPassword(rs.getString("password"));
                    sbyPassport.setNickname(rs.getString("nickname"));
                    sbyPassport.setQq(rs.getString("qq"));
                    sbyPassport.setEmail(rs.getString("email"));
                    sbyPassport.setCreateAt(rs.getTimestamp("createAt"));
                    sbyPassport.setPhoneNumber(rs.getString("phoneNumber"));
                    return sbyPassport;
                }else{
                    return null;
                }
            }
        });
    }

    @Override
    public List<SbyPassport> sbyPassportList() {
        String sql = "select * from sby_passport";
        return template.query(sql, new RowMapper<SbyPassport>() {
            public SbyPassport mapRow(ResultSet rs, int i) throws SQLException {
                SbyPassport sbyPassport = new SbyPassport();
                sbyPassport.setId(rs.getInt("id"));
                sbyPassport.setUsername(rs.getString("username"));
                sbyPassport.setPassword(rs.getString("password"));
                sbyPassport.setNickname(rs.getString("nickname"));
                sbyPassport.setQq(rs.getString("qq"));
                sbyPassport.setEmail(rs.getString("email"));
                sbyPassport.setCreateAt(rs.getTimestamp("createAt"));
                sbyPassport.setPhoneNumber(rs.getString("phoneNumber"));
                sbyPassport.setUserrole(rs.getInt("userrole"));
                return sbyPassport;
            }
        });
    }

    @Override
    public SbyPassport querypwd(Integer id, String password) {
        String sql = "select * from sby_passport where id=? and password=?";
        return template.query(sql,new Object[]{id,password},new ResultSetExtractor<SbyPassport>() {
            @Override
            public SbyPassport extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    SbyPassport sbyPassport = new SbyPassport();
                    sbyPassport.setId(rs.getInt("id"));
                    sbyPassport.setPassword(rs.getString("password"));
                    return sbyPassport;
                } else {
                    return null;
                }
            }
        });
    }

    @Override
    public Integer resetPwd(Integer id) {
        String sql = "update sby_passport set password='0000' where id=?";
        return template.update(sql,new Object[]{id});
    }

    @Override
    public Integer updatepwd(Integer id, String password) {
        String sql = "update sby_passport set password = '"+password+"' where id = "+id+"";
        return template.update(sql);
    }

    @Override
    public Integer insertuser(SbyPassport sbyPassport) {
        String sql = "insert into sby_passport(id,username,password,nickname,qq,email,phoneNumber,createAt) values(?,?,?,?,?,?,?,'"+createAt+"')";
        return template.update(sql, sbyPassport.getId(), sbyPassport.getUsername(), sbyPassport.getPassword(), sbyPassport.getNickname(), sbyPassport.getQq(), sbyPassport.getEmail(), sbyPassport.getPhoneNumber());
    }

    @Override
    public Integer updateuser(SbyPassport sbyPassport, Integer id) {
        String sql = "update sby_passport set username=?,nickname=?,qq=?,email=?,phoneNumber=?,createAt=? where id=?";
        return template.update(sql, sbyPassport.getUsername(), sbyPassport.getNickname(), sbyPassport.getQq(), sbyPassport.getEmail(), sbyPassport.getPhoneNumber(), createAt, sbyPassport.getId());
    }

    @Override
    public Integer deleteuser(Integer id) {
        String sql = "delete from sby_passport where id=?";
        return template.update(sql, id);
    }

    @Override
    public SbyPassport backstageloginuser(String username, String password) {
        String sql="select sp.id,sp.username,sp.password,susr.isFinance from sby_passport sp inner join sby_user_smsmanager_role susr on sp.id=susr.userId where sp.id=susr.userId and username = ? and password = ?";
        return template.query(sql,new Object[]{username,password},new ResultSetExtractor<SbyPassport>() {
            @Override
            public SbyPassport extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    SbyPassport sbyPassport = new SbyPassport();
                    sbyPassport.setId(rs.getInt("id"));
                    sbyPassport.setUsername(rs.getString("username"));
                    sbyPassport.setPassword(rs.getString("password"));
                    SmsManager smsManager=new SmsManager();
                    smsManager.setIsFinance(rs.getInt("isFinance"));
                    sbyPassport.setSmsManager(smsManager);
                    return sbyPassport;
                } else {
                    return null;
                }
            }
        });
    }

    @Override
    public SbyPassport optimizeloginuser(String username, String password) {
        String sql="select sp.id,sp.username,sp.password from sby_passport sp inner join sby_user_dataoptimize_role sudr on sp.id=sudr.userId where sp.id=sudr.userId and username = ? and password = ?";
        return template.query(sql,new Object[]{username,password},new ResultSetExtractor<SbyPassport>() {
            @Override
            public SbyPassport extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    SbyPassport sbyPassport = new SbyPassport();
                    sbyPassport.setId(rs.getInt("id"));
                    sbyPassport.setUsername(rs.getString("username"));
                    sbyPassport.setPassword(rs.getString("password"));
                    return sbyPassport;
                } else {
                    return null;
                }
            }
        });
    }
}
